package oop.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import oop.data.Category;
import oop.db.Database;

public final class CategoryDAO {

	public static List<Category> fetchByName(String name) throws SQLException {
		String sql = String.format("SELECT cat_id, cat_name, cat_parent "
				+ "FROM tblCategory " 
				+ "WHERE cat_name = '%s'", name);
		return fetch(sql);
	}

	public static List<Category> fetchById(int id) throws SQLException {
		String sql = "SELECT cat_id, cat_name, cat_parent "
				+ "FROM tblCategory " + "WHERE cat_id = " + id;
		return fetch(sql);
	}

	public static List<Category> fetchByParent(int id) throws SQLException {
		String sql = "SELECT cat_id, cat_name, cat_parent "
				+ "FROM tblCategory " + "WHERE cat_parent = " + id;
		return fetch(sql);
	}

	private static List<Category> fetch(String sql) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
	
			List<Category> categories = new ArrayList<Category>();
			while (rs.next()) {
				categories.add(new Category(rs.getInt(1), rs.getString(2), rs
						.getInt(3)));
			}
			return categories;
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static List<Category> fetchTopLevels() throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT cat_id, cat_name " 
					+ "FROM tblCategory "
					+ "WHERE cat_parent IS NULL";
	
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
	
			List<Category> categories = new ArrayList<Category>();
			while (rs.next()) {
				categories.add(new Category(rs.getInt(1), rs.getString(2), null));
			}
			return categories;
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static Category create(String name, Integer parentId)
			throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			String sql = "INSERT INTO tblCategory (cat_name, cat_parent) " +
					"VALUES (?, ?)";
			
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			if (parentId == null) {
				stmt.setNull(2, java.sql.Types.NUMERIC);
			} else {
				stmt.setInt(2, parentId);
			}
			stmt.executeUpdate();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			int id = rs.getInt(1);
			return new Category(id, name, parentId);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static void persist(Category category) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		try {
			String sql = String.format("UPDATE tblCategory SET cat_name='%s', cat_parent=%s " +
					"WHERE cat_id=%d", category.getName(), category.getParentId() == null ? "NULL" : category.getParentId().toString(), category.getId());

			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}
	
	public static int drop(long id) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			String sql = "DELETE FROM tblCategory WHERE cat_id = ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, id);
			return stmt.executeUpdate();
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

}
